//=============================================================================
// CoolStorage.NET - .NET Object Relational Mapping Library 
//
// Copyright (c) 2004-2007 Philippe Leybaert
//
// Permission is hereby granted, free of charge, to any person obtaining a copy 
// of this software and associated documentation files (the "Software"), to deal 
// in the Software without restriction, including without limitation the rights 
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 
// copies of the Software, and to permit persons to whom the Software is 
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in 
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 
// FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
// IN THE SOFTWARE.
//=============================================================================

using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Text.RegularExpressions;

namespace Activa.CoolStorage
{
    public class CSDataProviderAccess : CSDataProvider
    {
        public CSDataProviderAccess(string fileName)
            : base(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";")
        {
        }

        protected override IDbConnection CreateConnection()
        {
            OleDbConnection conn = new OleDbConnection(ConnectionString);

            conn.Open();

            return conn;
        }

        protected override CSDataProvider Clone()
        {
            return new CSDataProviderAccess(ConnectionString);
        }

        protected override IDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters)
        {
            OleDbCommand dbCommand = (OleDbCommand)Connection.CreateCommand();

            dbCommand.Transaction = (OleDbTransaction)CurrentTransaction;

            foreach (Match m in Regex.Matches(sqlQuery, "(?<!@)@[a-z_0-9]+", RegexOptions.IgnoreCase))
            {
                dbCommand.Parameters.AddWithValue(m.Value, ConvertParameter(parameters[m.Value].Value));
            }

            sqlQuery = Regex.Replace(sqlQuery, "(?<!@)@[a-z_0-9]+", "?", RegexOptions.IgnoreCase);

            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandText = sqlQuery;

            return dbCommand;
        }

        protected override string QuoteField(string fieldName) { return "[" + fieldName + "]"; }
        protected override string QuoteTable(string tableName) { return "[" + tableName + "]"; }

        protected override string NativeFunction(string functionName, ref string[] parameters)
        {
            switch (functionName.ToUpper())
            {
                default: return functionName.ToUpper();
            }
        }

        protected override bool SupportsNestedTransactions
        {
            get { return false; }
        }

        protected override string BuildGetKeys(string tableName, string[] columnList, string[] valueList, string[] primaryKeys, string identityField)
        {
            int id = 0;

            using (IDataReader reader = CreateReader("SELECT @@IDENTITY", null))
            {
                reader.Read();

                id = (int)reader[0];
            }

            if (primaryKeys != null && primaryKeys.Length > 0 && identityField != null)
                return String.Format("SELECT {0} from {1} where {2} = " + id, String.Join(",", QuoteFieldList(primaryKeys)), QuoteTable(tableName), identityField);

            return "";
        }

        protected override string BuildInsertSQL(string tableName, string[] columnList, string[] valueList,
                                                          string[] primaryKeys, string identityField)
        {
            string sql;

            if (columnList.Length > 0)
            {
                sql = String.Format("insert into {0} ({1}) values ({2})",
                                    QuoteTable(tableName),
                                    String.Join(",", QuoteFieldList(columnList)),
                                    String.Join(",", valueList)
                                    );
            }
            else
            {
                sql = String.Format("insert into {0} default values", QuoteTable(tableName));
            }

            return sql;

        }

        protected override string BuildSelectSQL(string tableName, string tableAlias, string[] columnList, string[] columnAliasList, string[] joinList, string whereClause, string orderBy, int startRow, int maxRows, bool quoteColumns, bool unOrdered)
        {
            StringBuilder sql = new StringBuilder(100);

            sql.Append("SELECT");

            if (maxRows > 0)
                sql.Append(" TOP " + maxRows);

            if (quoteColumns)
                columnList = QuoteFieldList(columnList);

            string[] columnNames = new string[columnList.Length];

            for (int i = 0; i < columnList.Length; i++)
            {
                columnNames[i] = columnList[i];

                if (columnAliasList != null)
                    columnNames[i] += " AS " + columnAliasList[i];
            }

            sql.Append(' ');
            sql.Append(String.Join(",", columnNames));
            sql.Append(" FROM ");

            if (joinList != null)
                sql.Append('(', joinList.Length);

            sql.Append(QuoteTable(tableName));
            sql.Append(' ');
            sql.Append(tableAlias);

            if (joinList != null && joinList.Length > 0)
                foreach (string joinExpression in joinList)
                    sql.Append(" " + joinExpression + ")");

            if (whereClause != null && whereClause.Length > 0)
                sql.Append(" WHERE " + whereClause);

            if (orderBy != null && orderBy.Length > 0)
                sql.Append(" ORDER BY " + orderBy);

            return sql.ToString();
        }

        protected override bool SupportsSequences
        {
            get { return false; }
        }

        protected override bool SupportsMultipleStatements
        {
            get { return false; }
        }

        protected override bool RequiresSeperateIdentityGet
        {
            get { return true; }
        }
    }
}
